<?php
/**
 * Pdo Mysql Adapter for Zend_Db_Tree_Nested 
 * TODO: I think we need abstract PDO adapter.
 * 
 * @category   Zend
 * @package    Zend_Db
 * @author     Steshenko Alexander (http://lcf.name)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 */
class Zend_Db_Tree_Nested_Adapter_Pdo_Mysql 
    extends Zend_Db_Tree_Nested_Adapter_Abstract 
{
    /**
     * Zend_Db_Select to row array convertation
     *
     * @param Zend_Db_Select $select
     * @return array
     */
    public function fetchRow(Zend_Db_Select $select)
    {
        return $this->_adapter->fetchRow($select, array(),
                                         Zend_Db::FETCH_ASSOC);
    }
    
    /**
     * Zend_Db_Select to array convertation
     *
     * @param Zend_Db_Select $select
     * @return array
     */
    public function fetchAll(Zend_Db_Select $select)
    {
        return $this->_adapter->fetchAll($select, array(), 
                                         Zend_Db::FETCH_ASSOC);
    }
    
    /**
     * Begin transaction method
     * 
     * You can use table lock for data bases
     * without transaction
     *
     */
    public function beginTransaction()
    {
        $this->_adapter->beginTransaction();
    }
    
    /**
     * Commit transaction method
     * 
     * You can use table lock for data bases
     * without transaction
     *
     */
    public function commitTransaction()
    {
        $this->_adapter->commit();
    }
    
    /**
     * Prepare Zend_Db_Select for return-type queries.
     *
     * @return Zend_Db_Select
     */
    protected function _getBaseReturnSelect()
    {
        $columns = array();
        $columnsCodes = array(
            Zend_Db_Tree_Nested::ID,
            Zend_Db_Tree_Nested::TREE_ID,
            Zend_Db_Tree_Nested::LEFT_KEY,
            Zend_Db_Tree_Nested::RIGHT_KEY,
            Zend_Db_Tree_Nested::LEVEL,
        );
        foreach ($columnsCodes as $columnCode){
            if (isset($this->_aliases[$columnCode]) && $this->_aliases[$columnCode] !== null) {
                $columns[$this->_aliases[$columnCode]] = $this->_columns[$columnCode];
            }
        }

        $select = $this->_adapter->select()
            ->from(array(Zend_Db_Tree_Nested::TABLE_ALIAS => $this->_tableName), $columns)
            ->where($this->_adapter->quoteIdentifier(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' 
                . $this->_columns[Zend_Db_Tree_Nested::TREE_ID]) . ' = ?', $this->_treeId)
            ->order(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]);

        return $select;
    }
    
    /**
     * Calculate free tree id in structure table
     *
     * @return integer
     */
    public function getFreeTreeId()
    {
        $treeId = $this->_adapter->fetchOne( 
            $this->_adapter->select()
                ->from($this->_tableName, 
                    array('treeId' => new Zend_Db_Expr(
                    	'MAX(' . $this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ')')))
        );
        if (!$treeId) {
            $treeId = 0;
        }
        
        return $treeId + 1;
    }
    
    /**
     * Get node information
     *
     * @param int $nodeId node id
     * @return Zend_Db_Select
     */
    public function getNode($nodeId)
    {
        $select = $this->_getBaseReturnSelect()
            ->where($this->_columns[Zend_Db_Tree_Nested::ID] . ' = ?', $nodeId);

        return $select;
    }

    /**
     * Get node information by its left key
     *
     * @param int $leftKey node's left key
     * @return Zend_Db_Select
     */
    public function getNodeByLeftKey($leftKey)
    {
        $select = $this->_getBaseReturnSelect()
            ->where($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' = ?', $leftKey);

        return $select;
    }

    /**
     * Get node information by its right key
     *
     * @param int $rightKey node's right key
     * @return Zend_Db_Select
     */
    public function getNodeByRightKey($rightKey)
    {
        $select = $this->_getBaseReturnSelect()
            ->where($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' = ?', $rightKey);
        return $select;
    }
    
    /**
     * Get value of minimal left key in the tree.
     * Actually it should always be equal 1
     *
     * @return int 
     */
    public function getMinLeftKey()
    {
        /*
         * TODO: 
         * check all columns and table references and add $this->_adapter->quoteIdentifier( everywhere!!!!
         */
        $minLeftKey = $this->_adapter->fetchOne( 
            $this->_adapter->select()
                ->from($this->_tableName, 
                    array('minLeftKey' => new Zend_Db_Expr(
                    	'MIN(' . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]) . ')')))
                ->where($this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::TREE_ID]) . ' = ?', 
                    $this->_treeId)
        );
        if (!$minLeftKey) {
            /*
             * If there is no any nodes in the tree,
             * minimal left key shouldn't exist. But for 
             * insertion methods and other we set it equal 1.
             */
            $minLeftKey = 1;
        }
        
        return $minLeftKey;
    }
    
    /**
     * Get maximal right key in the tree
     *
     * @return int
     */
    public function getMaxRightKey()
    {
        $maxRightKey = $this->_adapter->fetchOne( 
            $this->_adapter->select()
                ->from($this->_tableName, 
                    array('maxRightKey' => new Zend_Db_Expr(
                    	'MAX(' . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY]) . ')')))
                ->where($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ?', 
                    $this->_treeId)
        );
        if (!$maxRightKey) {
            $maxRightKey = 0;
        }
        
        return $maxRightKey;
    }
    
    /**
     * Insert node.
     *
     * @param int $leftKey left key
     * @param int $rightKey right key
     * @param int $level level
     * @return int new node id
     */
    public function insertNode($leftKey, $rightKey, $level)
    {
        $this->_adapter->insert(
            $this->_tableName, 
            array($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]  => $leftKey,
                  $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] => $rightKey,
                  $this->_columns[Zend_Db_Tree_Nested::LEVEL]     => $level,
                  $this->_columns[Zend_Db_Tree_Nested::TREE_ID]   => $this->_treeId)
        );
        $id = $this->_adapter->lastInsertId($this->_tableName);
        return $id;
    }
    
    /**
     * Update tree for node insertion
     * 
     * If its adjacent insert, $adjacent param has be true,
     * if its insert of the child, $adjacent param has to be false 
     * 
     * @param int $key right or left key (depends on place of insertion)
     * @param boolean $adjacent adjacent / child insertion
     */
    public function createUpdate($key, $adjacent = false) 
    {
        /*
         * Depending on the value of the $adjacent param
         * query use different comparison operands
         */
        $this->_adapter->update(
            $this->_tableName, 
            array(
                $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] => new Zend_Db_Expr(
                    $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' + 2'),
                $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] 
                    . ($adjacent ? ' >= ' : ' > ') . $key
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' + 2, '
                    . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ')')
            ),
            array(
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] 
                    . ($adjacent ? ' > ' : ' >= ') . $key),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] 
                    . ' = ' . $this->_treeId)
            )
        );
    }

    
    /**
     * One of the validation methods
     * Left key of the node is always lower than right key
     *
     * @return boolean
     */
    public function validateLeftRightKeys()
    {
        $select = $this->_adapter->select()
            ->from($this->_tableName)
            ->where($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ?', $this->_treeId)
            ->where($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]
                . '>=' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY]);
        
        $result = $this->_adapter->fetchRow($select);
        if ($result) {
            // There are some records in the result set. Tree is not valid
            return false;
        }
        
        return true;
    }
    
    /**
     * One of the validation methods
     * a) The lowest left key is always equal 1
     * b) The biggest right key is always equal number of nodes * 2
     *
     * @return boolean
     */
    public function validateMaxMinKeys()
    {
        $select = $this->_adapter->select()
            ->from($this->_tableName, array(
               	'nodesCount'  => new Zend_Db_Expr('COUNT(' 
                    . $this->_columns[Zend_Db_Tree_Nested::ID] . ')'),
           		'minLeftKey'  => new Zend_Db_Expr('MIN(' 
                    . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ')'),
                'maxRightKey' => new Zend_Db_Expr('MAX(' 
                    . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ')')
            ))
            ->where($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ?',
                    $this->_treeId);

        $result = $this->_adapter->fetchRow($select);
        $maxRightKey = $result['maxRightKey'];
        $minLeftKey  = $result['minLeftKey'];
        $nodesCount  = $result['nodesCount'];

        if ($minLeftKey != 1) {
            // minimal key is not equal 1
            return false;
        }
        if ($maxRightKey != $nodesCount * 2) {
            // maximal key is not double nodes number
            return false;
        }
        
        return true;
    }
    
    /**
     * One of the validation methods:
     * a) difference between right and left key are always odd
     * b) If level of the node is odd then the left key are always even 
     * 		(and same for even levels)
     *
     * @return boolean
     */
    public function validateKeysDiff()
    {
        $select = $this->_adapter->select()
            ->from($this->_tableName, array(
               	$this->_columns[Zend_Db_Tree_Nested::ID],
           		'diff' => new Zend_Db_Expr(
               	    'MOD((' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY]
               	    . ' - ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . '), 2)'
               	)
            ))
            ->having('diff = ?', 0)
            ->where($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ?', 
                    $this->_treeId);

        $result = $this->_adapter->fetchRow($select);
        if ($result) {
            // There are some records in the result set. Tree is not valid.
            return false;
        }

        $select = $this->_adapter->select()
            ->from($this->_tableName, array(
               	$this->_columns[Zend_Db_Tree_Nested::ID],
           		'diff' => new Zend_Db_Expr(
               	    'MOD((' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]
               	    . ' - ' . $this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' + 2), 2)'
               	)
            ))
            ->having('diff = ?', 1)
            ->where($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ?', 
                    $this->_treeId);

        $result = $this->_adapter->fetchRow($select);
        if ($result) {
            // There are some records in the result set. Tree is not valid.
            return false;
        }
        
        return true;
    }
    
    /**
     * One of the validation methods:
     * Keys are always uniq (independent is it left or right)
     * 
     * @return boolean
     */
    public function validateUniqKeys()
    {
        $sql = Zend_Db_Select::SQL_SELECT . ' '
        	 . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::ID]) 
        	 . ', COUNT(' 
        	 . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::ID]) 
        	 . ') ' . Zend_Db_Select::SQL_AS . ' rep, '
        	 . 'MAX('
             . $this->_adapter->quoteIdentifier('t3.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY])
             . ') ' . Zend_Db_Select::SQL_AS . ' max_right '
             . Zend_Db_Select::SQL_FROM . ' '
             . $this->_adapter->quoteIdentifier($this->_tableName)
             . ' ' . Zend_Db_Select::SQL_AS . ' t1, '
             . $this->_adapter->quoteIdentifier($this->_tableName)
             . ' ' . Zend_Db_Select::SQL_AS . ' t2, '
             . $this->_adapter->quoteIdentifier($this->_tableName)
             . ' ' . Zend_Db_Select::SQL_AS . ' t3 '
             . Zend_Db_Select::SQL_WHERE . ' '
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY])
             . ' <> '
             . $this->_adapter->quoteIdentifier('t2.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY])
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY])
             . ' <> '
             . $this->_adapter->quoteIdentifier('t2.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY])  
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY])
             . ' <> '
             . $this->_adapter->quoteIdentifier('t2.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY])  
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY])
             . ' <> '
             . $this->_adapter->quoteIdentifier('t2.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY])  
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::TREE_ID])
             . ' = ' . $this->_adapter->quote($this->_treeId)
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t2.' . $this->_columns[Zend_Db_Tree_Nested::TREE_ID])
             . ' = ' . $this->_adapter->quote($this->_treeId)
             . ' ' .Zend_Db_Select::SQL_AND . ' '
             . $this->_adapter->quoteIdentifier('t3.' . $this->_columns[Zend_Db_Tree_Nested::TREE_ID])
             . ' = ' . $this->_adapter->quote($this->_treeId)
             . ' ' . Zend_Db_Select::SQL_GROUP_BY
             . $this->_adapter->quoteIdentifier('t1.' . $this->_columns[Zend_Db_Tree_Nested::ID])
             . ' ' . Zend_Db_Select::SQL_HAVING
             . ' max_right <> SQRT(4 * rep + 1) + 1';

        $result = $this->_adapter->fetchRow($sql);
        if ($result) {

            return false;
        }          
             
        return true;
    }
    
    /**
     * Delete node and all children by left and right keys
     *
     * @param int $leftKey
     * @param int $righKey 
     */
    public function delete($leftKey, $rightKey)
    {
        $this->_adapter->delete(
            $this->_tableName,
            array(
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' >= ' . $leftKey),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= ' . $rightKey),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ' . $this->_treeId)
            )
        );
    }
    
    /**
     * Delete data from relative table
     * 
     * @param array $ids array with deleted nodes id
     * @return boolean
     */
    public function deleteCallback($ids)
    {
        if ($this->_relativeTableName === null) {
            return false;
        }
        return $this->_adapter->delete(
            $this->_relativeTableName,
            new Zend_Db_Expr(
                $this->_adapter->quoteIdentifier($this->_relativeKey)
                . ' IN (' . implode(',', $ids) . ')' 
            )
        );
    }
    
    /**
     * Update tree after deletion
     *
     * @param int $leftKey left key deleted node
     * @param int $righKey right key deleted node
     */
    public function deleteUpdate($leftKey, $rightKey)
    {
        $this->_adapter->update(
            $this->_tableName, 
            array(
                $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] => new Zend_Db_Expr(
                    $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' - (' . $rightKey
                    . ' - ' . $leftKey . ' + 1)'),

                $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' > ' . $leftKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' - ('
                    . $rightKey . ' - ' . $leftKey . ' + 1 ), '
                    . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ')')
            ),
            array(
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' > ' . $rightKey),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ' . $this->_treeId)
            )
        );
    }
    
    /**
     * Delete tree
     *
     * @return boolean
     */
    public function deleteTree()
    {
        $this->_adapter->delete(
            $this->_tableName,
            new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] 
                . ' = ' . $this->_treeId)
        );
        
        return true;
    }
    
    /**
     * Get whole tree
     *
     * @return Zend_Db_Select
     */
    public function getTree()
    {
        $select = $this->_getBaseReturnSelect();
        
        return $select;
    }
    
    /**
	 * Get path to node
     *
     * @param int $leftKey left key
     * @param int $rightKey right key
     * @return Zend_Db_Select
     */
    public function getPath($leftKey, $rightKey)
    {
        $select = $this->_getBaseReturnSelect()
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' <= ' . $leftKey)
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' >= ' . $rightKey);
            
        return $select;
    }
    
    /**
     * Get open tree.
     * Open tree is all root nodes, path to the node and children of the node.
     *
     * @param int $leftKey left key
     * @param int $rightKey right key
     * @param int $level node level
     * @return Zend_Db_Select
     */
    public function getOpenTree($leftKey, $rightKey, $level)
    {
        
        $select = $this->_getBaseReturnSelect()
            ->join(array('tmp' => $this->getPath($leftKey, $rightKey)),
                		 '((tmp.' . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY]) 
                         . ' > ' . Zend_Db_Tree_Nested::TABLE_ALIAS . '.' 
                         . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY]) . ') ' 
                         . Zend_Db_Select::SQL_AND
                         . ' (tmp.' . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]) 
                         . ' < ' . Zend_Db_Tree_Nested::TABLE_ALIAS . '.' 
                         . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY]) . ') '
                         . Zend_Db_Select::SQL_AND
                         . ' (tmp.' . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEVEL]) 
                         . ' = ' . Zend_Db_Tree_Nested::TABLE_ALIAS . '.' 
                         . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEVEL]) . ' - 1)) '
                         . Zend_Db_Select::SQL_OR
                         . '(' . Zend_Db_Tree_Nested::TABLE_ALIAS . '.' 
                         . $this->_adapter->quoteIdentifier($this->_columns[Zend_Db_Tree_Nested::LEVEL]) . ' = 1)', array())
             ->group(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::ID]);

        return $select;
    }

    /**
     * Get parent node's information
     *
     * @param int $leftKey
     * @param int $rightKey
     * @param int $level
     * @return Zend_Db_Select
     */
    public function getParent($leftKey, $rightKey, $level)
    {
        $select = $this->_getBaseReturnSelect()
            ->where($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' <= ' . $leftKey)
            ->where($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' >= ' . $rightKey)
            ->where($this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' = ' . ($level - 1));

		return $select;
    }
    
    /**
     * Get root nodes (with level = 1)
     *
     * @return Zend_Db_Select
     */
    public function getRootNodes()
    {
        $select = $this->_getBaseReturnSelect()
            ->where($this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' = 1');

        return $select;
    }

    /**
    /**
     * Get children of the node. 
     * Note, that not all descendant will be selected, but only
     * children - descendant with level = parent level + 1
     *
     * @param int $leftKey
     * @param int $rightKey
     * @param int $level
     * @return Zend_Db_Select
     */
    public function getChildren($leftKey, $rightKey, $level)
    {
        $select = $this->getDescendants($leftKey, $rightKey)
            ->where($this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' = ' . ($level + 1));

        return $select;
    }
    
    /**
     * Get all descendants of the node and the node itself (branch)
     *
     * @param int $leftKey
     * @param int $rightKey 
     * @return Zend_Db_Select
     */
    public function getBranch($leftKey, $rightKey)
    {
        // TODO: нужно везде проверить и разместить чтобы в условиях where обязательно была прописана таблица с деревом, иначе может быть ошибка при внешнем джойне.
        $select = $this->_getBaseReturnSelect()
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' >= ' . $leftKey)
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= ' . $rightKey);
            
        return $select;
    }
    
    /**
     * Get all descendants of the node
     *
     * @param int $leftKey
     * @param int $rightKey 
     * @return Zend_Db_Select
     */
    public function getDescendants($leftKey, $rightKey)
    {
        $select = $this->_getBaseReturnSelect()
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' > ' . $leftKey)
            ->where(Zend_Db_Tree_Nested::TABLE_ALIAS . '.' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' < ' . $rightKey);
            
        return $select;
    }
    
    /**
     * Move node up on the tree
     *
     * @param int $leftKey moved node left key
     * @param int $rightKey moved node right key
     * @param int $skewTree keys' offset
     * @param int $skewLevel level's offset
     * @param int $near "point of movement"
     * @return boolean
     */
    public function moveNodeUp($leftKey, $rightKey, $skewTree, $skewLevel, $near)
    {
        // Tree's keys' offset 
        $skewEdit = $near - $leftKey + 1 - $skewTree;
        // Move node and update the tree
        $this->_adapter->update(
            $this->_tableName,
            array(
                $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= ' . $rightKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' + ' . $skewEdit
                    . ', IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' > ' . $rightKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' - ' . $skewTree
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . '))'),

                $this->_columns[Zend_Db_Tree_Nested::LEVEL] => new Zend_Db_Expr(
                	'IF(' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= ' . $rightKey 
                	. ', ' . $this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' + ' 
                	. $skewLevel . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEVEL] . ')'),

                $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= ' . $rightKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' + '
                    . $skewEdit . ', IF(' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' <= '
                    . $near . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' - ' . $skewTree
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . '))')
            ),
            array(
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' > ' . $leftKey),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' <= ' . $near),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ' . $this->_treeId)
            )            
        );
        return true;
    }
    
    /**
     * Move node down on the tree
     *
     * @param int $leftKey moved node left key
     * @param int $rightKey moved node right key
     * @param int $skewTree keys' offset
     * @param int $skewLevel level's offset
     * @param int $near "point of movement"
     * @return boolean
     */
    public function moveNodeDown($leftKey, $rightKey, $skewTree, $skewLevel, $near)
    {
        $skewEdit = $near - $leftKey + 1;
        $this->_adapter->update(
            $this->_tableName,
            array(
                $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' >= ' . $leftKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' + ' . $skewEdit
                    . ', IF(' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' < ' . $leftKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' + ' . $skewTree
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . '))'),

                $this->_columns[Zend_Db_Tree_Nested::LEVEL] => new Zend_Db_Expr(
                	'IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' >= ' . $leftKey 
                	. ', ' . $this->_columns[Zend_Db_Tree_Nested::LEVEL] . ' + ' 
                	. $skewLevel . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEVEL] . ')'),

                $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] => new Zend_Db_Expr(
                    'IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' >= ' . $leftKey
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' + '
                    . $skewEdit . ', IF(' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' > '
                    . $near . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' + ' . $skewTree
                    . ', ' . $this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . '))')
            ),
            array(
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::RIGHT_KEY] . ' > ' . $near),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::LEFT_KEY] . ' < ' . $rightKey),
                new Zend_Db_Expr($this->_columns[Zend_Db_Tree_Nested::TREE_ID] . ' = ' . $this->_treeId)
            )
        );
        return true;
    }
}